package com.kingray.openfire.plugin.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

import net.sf.json.JSONArray;
import net.sf.json.JsonConfig;

import org.jivesoftware.database.DbConnectionManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xmpp.packet.Message;

import com.kingray.openfire.plugin.DBConnection;
import com.kingray.openfire.plugin.dao.MessageDAO;
import com.kingray.openfire.plugin.vo.MessageVO;
import com.kingray.openfire.plugin.vo.PageVO;
import com.xiongyingqi.util.CalendarHelper;
import com.xiongyingqi.util.DateHelper;
import com.xiongyingqi.util.StringHelper;

public class MessageDAOImpl implements MessageDAO {
	private SimpleDateFormat formatter = new SimpleDateFormat("yyyy.MM.dd  HH:mm:ss");
	public static final Logger log = LoggerFactory
			.getLogger(MessageDAOImpl.class);
	/**
	 * 插入默认长度历史消息sql
	 */
	private static final String ADD_MESSAGE_HISTORY_SQL = "insert into kr_message_history(send_message_id, message_from, message_to, message_subject, message_body, message_type) values(?, ?, ?, ?, ?, ?)";
	/**
	 * 插入大长度历史消息sql
	 */
	private static final String ADD_LONG_MESSAGE_HISTORY_BODY_SQL = "insert into kr_long_message_history(long_message_body) values(?)";
	private static final String ADD_LONG_MESSAGE_HISTORY_SQL = "insert into kr_message_history(send_message_id, message_from, message_to, long_message_id, message_type) values(?, ?, ?, ?, ?)";
	/**
	 * 插入超长长度历史消息sql
	 */
	private static final String ADD_LARGEST_MESSAGE_HISTORY_BODY_SQL = "insert into kr_largest_message_history(largest_message_body) values(?)";
	private static final String ADD_LARGEST_MESSAGE_HISTORY_SQL = "insert into kr_message_history(send_message_id, message_from, message_to, largest_message_id, message_type) values(?, ?, ?, ?, ?)";

	/**
	 * 消息总数
	 */
	public static final String RECORD_COUNT_SQL = "select count(message_id) from kr_message_history where 1=1 ";
	/**
	 * 消息历史查询
	 */
	private static final String SELECT_MESSAGE_HISTORY_SQL = "select message_id, long_message_id, largest_message_id, message_from, message_to, message_subject, message_body, message_date_time, send_message_id, message_type from kr_message_history where 1 = 1 ";
	/**
	 * 查询消息内容
	 */
	private static final String SELECT_LONG_MESSAGE_HISORY_SQL = "select long_message_body from kr_long_message_history where long_message_id = ?";
	/**
	 * 查询超长消息内容
	 */
	private static final String SELECT_LARGEST_MESSAGE_HISORY_SQL = "select largest_message_body from kr_largest_message_history where largest_message_id = ?";

	/**
	 * 长消息模糊查询
	 */
	private static final String FUZZY_SELECT_LONG_MESSAGE_HISORY_SQL = "select long_message_id, long_message_body from kr_long_message_history where locate(?, long_message_body) > 0";
	private static final String SELECT_MESSAGE_HISTORY_BY_LONG_MESSAGE_ID = "select message_id, message_from, message_to, message_body from kr_message_history where long_message_id = ?";
	/**
	 * 超长消息模糊查询
	 */
	private static final String FUZZY_SELECT_LARGEST_MESSAGE_HISORY_SQL = "select largest_message_id, largest_message_body from kr_largest_message_history where locate(?, largest_message_body) > 0";
	private static final String SELECT_MESSAGE_HISTORY_BY_LARGEST_MESSAGE_ID = "select message_id, message_from, message_to, message_body from kr_message_history where largest_message_id = ?";

	private static final String SELECT_MESSAGE_HISTORY_BY_ID = "SELECT  message_id, long_message_id, largest_message_id, message_from, message_to, message_subject, message_body, message_date_time FROM kr_message_history where message_id = ?";
	/**
	 * 根据用户名查询
	 */
	private static final String SELECT_MESSAGE_HISTORY_COUNT_BY_USER_NAME = "SELECT count(*) from kr_message_history where ((message_from = ? and message_to like ?) or (message_from like ? and message_to = ?)) ";
	
	/**
	 * 查询消息概要内容
	 */
	private static final String SELECT_MESSAGE_HISTORY_SUMMARY_BY_USER_NAME = "SELECT message_id, send_message_id from kr_message_history where ((message_from = ? and message_to like ?) or (message_from like ? and message_to = ?)) ";

	/**
	 * 根据用户名查询消息详细内容
	 */
	private static final String SELECT_MESSAGE_HISTORY_DETAIL_BY_USER_NAME = "select message_id, long_message_id, largest_message_id, message_from, message_to, message_subject, message_body, message_date_time, send_message_id, message_type from kr_message_history where ((message_from = ? and message_to like ?) or (message_from like ? and message_to = ?)) ";
	
	/**
	 * 根据消息ID查询消息详细内容
	 */
	private static final String SELECT_MESSAGE_HISTORY_DETAIL_BY_MESSAGE_ID = "select message_id, long_message_id, largest_message_id, message_from, message_to, message_subject, message_body, message_date_time, send_message_id, message_type from kr_message_history where message_id = ? ";

	/**
	 * 根据发送消息ID查询消息详细内容
	 */
	private static final String SELECT_MESSAGE_HISTORY_DETAIL_BY_SEND_MESSAGE_ID = "select message_id, long_message_id, largest_message_id, message_from, message_to, message_subject, message_body, message_date_time, send_message_id, message_type from kr_message_history where send_message_id = ? ";
	
	/**
	 * 排序映射表
	 */
	private static Map<String, String> oderByMap;
	
	protected static Connection connection;
	
	public Connection getConnection(){
		try {
			connection = DbConnectionManager.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return connection;
//		return DBConnection.getConnection();
	}
	
	/**
	 * 插入消息历史
	 * 
	 * @param messageVO
	 * @return
	 */
	public boolean addMessageHistory(MessageVO messageVO) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = getConnection();
			String body = messageVO.getMessageBody();
			if (body.length() <= 64) {
				preparedStatement = connection
						.prepareStatement(ADD_MESSAGE_HISTORY_SQL);
				preparedStatement.setString(1, messageVO.getSendMessageId());
				preparedStatement.setString(2, messageVO.getMessageFrom());
				preparedStatement.setString(3, messageVO.getMessageTo());
				preparedStatement.setString(4, messageVO.getMessageSubject());
				preparedStatement.setString(5, messageVO.getMessageBody());
				preparedStatement.setString(6, messageVO.getMessageType());
				preparedStatement.executeUpdate();
			} else if (body.length() <= 65535) { // 长消息存储
				preparedStatement = connection.prepareStatement(
						ADD_LONG_MESSAGE_HISTORY_BODY_SQL,
						PreparedStatement.RETURN_GENERATED_KEYS); // 返回自动生成的主键键值
				preparedStatement.setString(1, body);
				preparedStatement.executeUpdate();
				int id = -1;
				ResultSet rs = preparedStatement.getGeneratedKeys();
				while (rs.next()) {
					id = rs.getInt(1);
				}
				if (id > -1) { // 如果返回正确的id值
					preparedStatement = connection
							.prepareStatement(ADD_LONG_MESSAGE_HISTORY_SQL); // 返回自动生成的主键键值
					
					preparedStatement.setString(1, messageVO.getSendMessageId());
					preparedStatement.setString(2, messageVO.getMessageFrom());
					preparedStatement.setString(3, messageVO.getMessageTo());
					preparedStatement.setInt(4, id);
					preparedStatement.setString(5, messageVO.getMessageType());
					preparedStatement.executeUpdate();
				}
			} else if (body.length() <= 4294967295L) { // 超长消息存储
				preparedStatement = connection.prepareStatement(
						ADD_LARGEST_MESSAGE_HISTORY_BODY_SQL,
						PreparedStatement.RETURN_GENERATED_KEYS); // 返回自动生成的主键键值
				preparedStatement.setString(1, body);
				preparedStatement.executeUpdate();
				int id = -1;
				ResultSet rs = preparedStatement.getGeneratedKeys();
				while (rs.next()) {
					id = rs.getInt(1);
				}
				if (id > -1) { // 如果返回正确的id值
					preparedStatement = connection
							.prepareStatement(ADD_LARGEST_MESSAGE_HISTORY_SQL); // 返回自动生成的主键键值
					preparedStatement.setString(1, messageVO.getSendMessageId());
					preparedStatement.setString(2, messageVO.getMessageFrom());
					preparedStatement.setString(3, messageVO.getMessageTo());
					preparedStatement.setInt(4, id);
					preparedStatement.setString(5, messageVO.getMessageType());
					
					preparedStatement.executeUpdate();
				}
			} else {
				log.error(messageVO.toString());
				return false;
			}

		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
			log.warn("bodylength: " + messageVO.getMessageBody().length());
			log.warn("messageVO: " + messageVO);
			return false;
		} finally {
			DbConnectionManager.closeConnection(preparedStatement, connection);
		}
		return true;
	}

	@Override
	public PageVO getMessageHistory(PageVO inputPageVO,
			MessageVO inputMessageVO, String dateFrom, String dateTo,
			String[] orderBys, int asc) {
		if (oderByMap == null) {
			oderByMap = new HashMap<String, String>();
			oderByMap.put("message_id", "message_id");
			oderByMap.put("message_from", "message_from");
			oderByMap.put("message_to", "message_to");
			oderByMap.put("message_subject", "message_subject");
			oderByMap.put("message_body", "message_body");
			oderByMap.put("message_date_time", "message_date_time");
			oderByMap.put("messageId", "message_id");
			oderByMap.put("messageFrom", "message_from");
			oderByMap.put("messageTo", "message_to");
			oderByMap.put("messageSubject", "message_subject");
			oderByMap.put("messageBody", "message_body");
			oderByMap.put("messageDateTime", "message_date_time");
			oderByMap.put("messageDateTimeStr", "message_date_time");
		}

		
		ArrayList<MessageVO> messageVOs = new ArrayList<MessageVO>();
		PageVO pageVO = new PageVO();
		Connection connection = null;
		PreparedStatement ps = null;
		try {
//			connection = DbConnectionManager.getConnection();
			connection = getConnection();
			// ------------------------------- 查询条件 -------------------------------
			StringBuilder conditions = new StringBuilder(); // 查询条件sql语句
			
			String from = null;
			String to = null;
			String subject = null;
			String body = null;
			if (inputMessageVO != null) {
				from = inputMessageVO.getMessageFrom();
				to = inputMessageVO.getMessageTo();
				subject = inputMessageVO.getMessageSubject();
				body = inputMessageVO.getMessageBody();

			}
			if (from != null && !"".equals(from)) { // 发送者
				conditions.append(" and locate('" + from
						+ "' , message_from) > 0 ");
			}
			if (to != null && !"".equals(to)) { // 接受者
				conditions.append(" and locate('" + to + "', message_to) > 0 ");
			}

			if (subject != null && !"".equals(subject)) { // 短消息标题
				conditions.append(" and ( "); // 方便连接 "or"

				StringTokenizer tokenizer = new StringTokenizer(subject); // 空格分隔符
				int i = 0;
				while (tokenizer.hasMoreTokens()) {
					String subjectEmp = tokenizer.nextToken();
					StringTokenizer commaTokenizer = new StringTokenizer(
							subjectEmp, ","); // 逗号分隔符
					while (commaTokenizer.hasMoreTokens()) {
						String subjectRst = commaTokenizer.nextToken(); // 最后分隔完成
						
						if(i++ == 0){
							conditions.append(" locate('" + subjectRst
									+ "' , message_subject) > 0 ");
						} else {
							conditions.append(" or locate('" + subjectRst
									+ "' , message_subject) > 0 ");
						}
						
					}
				}
				conditions.append(") ");
			}

			if (body != null && !"".equals(body)) { // 短消息内容
				conditions.append("and ("); // 方便连接 "or"

				StringTokenizer tokenizer = new StringTokenizer(body); // 空格分隔符
				int i = 0;
				while (tokenizer.hasMoreTokens()) {
					String bodyEmp = tokenizer.nextToken();
					StringTokenizer commaTokenizer = new StringTokenizer(
							bodyEmp, ","); // 逗号分隔符
					while (commaTokenizer.hasMoreTokens()) {
						String bodyRst = commaTokenizer.nextToken(); // 最后分隔完成
						if(i++ == 0){
							conditions.append(" locate('" + bodyRst
									+ "' , message_body) > 0 ");
						} else {
							conditions.append(" or locate('" + bodyRst
									+ "' , message_body) > 0 ");
						}
					}
				}
				conditions.append(") ");
			}

			if (dateFrom != null) { // 日期范围
				conditions.append("and message_date_time >= '" + dateFrom
						+ "' ");
			}
			if (dateTo != null) { // 日期范围
				conditions.append("and message_date_time <= '" + dateTo + "' ");
			}
			//end ------------------------------- 查询条件结束 -------------------------------
			
			
			
			//start ------------------------------- 查询总数 -------------------------------
			int recordCount = 0;
			StringBuilder recordCountSQL = new StringBuilder(RECORD_COUNT_SQL);
			recordCountSQL.append(conditions);
			System.out.println("recordCountSQL.toString() ========== " + recordCountSQL.toString());
			ps = connection.prepareStatement(recordCountSQL.toString());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				recordCount = rs.getInt(1);
			}

			int pageNumber = inputPageVO.getPageNumber(); // 页码
			int pageSize = inputPageVO.getPageSize(); // 分页记录数
			if (pageSize <= 0) {
				pageSize = 20;
			}
			int pageCount = recordCount / pageSize; // 总分页数
			int leastRecord = recordCount % pageSize; // 如果不能整除
			if (leastRecord > 0) {
				pageCount++;
			}
			if (pageNumber > pageCount) {
				pageNumber = pageCount;
			}
			int recordFromIndex = (pageNumber - 1) * pageSize; // 起始索引
			//end ------------------------------- 查询总数结束 -------------------------------
			
			
			
			// ------------------------------- 短消息检索 -------------------------------
			if(recordCount > 0){
				StringBuilder sqlBuilder = new StringBuilder(
						SELECT_MESSAGE_HISTORY_SQL);
				sqlBuilder.append(conditions);
				// 排序
				if (orderBys != null && orderBys.length > 0) {
					int length = orderBys.length;
					
					List<String> keywordses = new ArrayList<String>();
					for (int i = 0; i < length; i++) { // 获取有意义的关键字
						String orderBy = orderBys[i];
						if (!"".equals(orderBy)) {
							String keywords = oderByMap.get(orderBy);
							keywordses.add(keywords);
						}
					}
					
					Iterator<String> iterator = keywordses.iterator();
					sqlBuilder.append(" order by ");
					int i = 0;
					int size = keywordses.size();
					while (iterator.hasNext()) {
						String orderBy = (String) iterator.next();
						if (!"".equals(orderBy)) {
							sqlBuilder.append(orderBy);
							if (i++ < size - 1) { // 如果不是最后一个关键字
								sqlBuilder.append(", ");
							}
						}
					}
					
					// 倒序或升序
					if (asc >= 1) {
						sqlBuilder.append(" asc");
					} else {
						sqlBuilder.append(" desc");
					}
				}
				sqlBuilder.append(" limit ?, ?");
//			System.out.println(sqlBuilder.toString());
				log.debug("getMessageHistory sql: " + sqlBuilder.toString());
				ps = connection.prepareStatement(sqlBuilder.toString());
				ps.setInt(1, recordFromIndex);
				ps.setInt(2, pageSize);
				ResultSet messageResultSet = ps.executeQuery();
				while (messageResultSet.next()) {
					long messageId = messageResultSet.getLong(1);
					String longMessageIdStr = messageResultSet.getString(2);
					String largestMessageIdStr = messageResultSet.getString(3);
					String mssageFrom = messageResultSet.getString(4);
					String mssageTo = messageResultSet.getString(5);
					String messageSubject = messageResultSet.getString(6);
					String messageBody = messageResultSet.getString(7);
					String sendMessageId = messageResultSet.getString(8);
					String messageType = messageResultSet.getString(9);
					// Date messageDateTime = new Date();
					Date messageDateTime = new Date(messageResultSet
							.getTimestamp(8).getTime());
					if (longMessageIdStr != null) { // 如果包含长文本外键
//					System.out.println("longMessageIdStr ===== "
//							+ longMessageIdStr);
						PreparedStatement longMessagePreparedStatement = null;
						try {
							int longMessageId = Integer.parseInt(longMessageIdStr);
							longMessagePreparedStatement = connection
									.prepareStatement(SELECT_LONG_MESSAGE_HISORY_SQL);
							longMessagePreparedStatement.setInt(1, longMessageId);
							ResultSet longMessageResultSet = longMessagePreparedStatement
									.executeQuery();
							if (longMessageResultSet.next()) {
								messageBody = longMessageResultSet.getString(1);
							}
						} catch (NumberFormatException e) {
							log.error(e.getMessage(), e);
						} finally {
							longMessagePreparedStatement.close();
						}
					} else if (largestMessageIdStr != null) { // 如果包含超长文本外键
//					System.out.println("largestMessageIdStr ===== "
//							+ largestMessageIdStr);
						PreparedStatement largestMessagePreparedStatement = null;
						try {
							int largestMessageId = Integer
									.parseInt(largestMessageIdStr);
							largestMessagePreparedStatement = connection
									.prepareStatement(SELECT_LARGEST_MESSAGE_HISORY_SQL);
							largestMessagePreparedStatement.setInt(1,
									largestMessageId);
							ResultSet largestMessageResultSet = largestMessagePreparedStatement
									.executeQuery();
							if (largestMessageResultSet.next()) {
								messageBody = largestMessageResultSet.getString(1);
							}
						} catch (NumberFormatException e) {
							log.error(e.getMessage(), e);
						} finally {
							largestMessagePreparedStatement.close();
						}
					}
					MessageVO messageVO = new MessageVO();
					messageVO.setMessageId(messageId);
					messageVO.setMessageFrom(mssageFrom);
					messageVO.setMessageTo(mssageTo);
					messageVO.setMessageSubject(messageSubject);
					messageVO.setMessageBody(messageBody);
					messageVO.setMessageDateTime(messageDateTime);
					messageVO.setSendMessageId(sendMessageId);
					messageVO.setMessageType(messageType);
					messageVOs.add(messageVO);
				}
			}
			pageVO.setPageCount(pageCount);
			pageVO.setPageNumber(pageNumber);
			pageVO.setRecordCount(recordCount);
			pageVO.setPageSize(pageSize);
			pageVO.setPageContents(messageVOs);

			/**
			 * 长消息检索 ps = connection.prepareStatement(
			 * FUZZY_SELECT_LONG_MESSAGE_HISORY_SQL); ps.setString(1, body);
			 * ResultSet longMessageResultSet = ps.executeQuery();
			 * while(longMessageResultSet.next()){ int longMessageId =
			 * longMessageResultSet.getInt(1); String longMessageBody =
			 * longMessageResultSet.getString(2); ps =
			 * connection.prepareStatement
			 * (SELECT_MESSAGE_HISTORY_BY_LONG_MESSAGE_ID); ps.setInt(1,
			 * longMessageId); ResultSet empLongMessageResultSet =
			 * ps.executeQuery(); while(empLongMessageResultSet.next()){
			 * 
			 * } }
			 */
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		
//		
//		try {
//			connection = DbConnectionManager.getConnection();
//			// connection = DBConnection.getConnection();
//
//			// 查询总数
//			int recordCount = 0;
//			ps = connection.prepareStatement(RECORD_COUNT_SQL);
//			ResultSet rs = ps.executeQuery();
//			if (rs.next()) {
//				recordCount = rs.getInt(1);
//			}
//
//			int pageNumber = inputPageVO.getPageNumber(); // 页码
//			int pageSize = inputPageVO.getPageSize(); // 分页记录数
//			if (pageSize <= 0) {
//				pageSize = 20;
//			}
//			int pageCount = recordCount / pageSize; // 总分页数
//			int leastRecord = recordCount % pageSize; // 如果不能整除
//			if (leastRecord > 0) {
//				pageCount++;
//			}
//			if (pageNumber > pageCount) {
//				pageNumber = pageCount;
//			}
//
//			/**
//			 * 短消息检索
//			 */
//			StringBuilder sqlBuilder = new StringBuilder(
//					SELECT_MESSAGE_HISTORY_SQL);
//			int recordFromIndex = (pageNumber - 1) * pageSize; // 起始索引
//			String from = null;
//			String to = null;
//			String subject = null;
//			String body = null;
//			if (inputMessageVO != null) {
//				from = inputMessageVO.getMessageFrom();
//				to = inputMessageVO.getMessageTo();
//				subject = inputMessageVO.getMessageSubject();
//				body = inputMessageVO.getMessageBody();
//
//			}
//
//			if (from != null && !"".equals(from)) { // 发送者
//				sqlBuilder.append(" and locate('" + from
//						+ "' , message_from) > 0 ");
//			}
//			if (to != null && !"".equals(to)) { // 接受者
//				sqlBuilder
//						.append(" and locate('" + to + "' , message_to) > 0 ");
//			}
//
//			if (subject != null && !"".equals(subject)) { // 短消息标题
//				sqlBuilder.append(" and ( "); // 方便连接 "or"
//
//				StringTokenizer tokenizer = new StringTokenizer(subject); // 空格分隔符
//				int i = 0;
//				while (tokenizer.hasMoreTokens()) {
//					String subjectEmp = tokenizer.nextToken();
//					StringTokenizer commaTokenizer = new StringTokenizer(
//							subjectEmp, ","); // 逗号分隔符
//					while (commaTokenizer.hasMoreTokens()) {
//						String subjectRst = commaTokenizer.nextToken(); // 最后分隔完成
//						
//						if(i++ == 0){
//							sqlBuilder.append(" locate('" + subjectRst
//									+ "' , message_subject) > 0 ");
//						} else {
//							sqlBuilder.append(" or locate('" + subjectRst
//									+ "' , message_subject) > 0 ");
//						}
//						
//					}
//				}
//				sqlBuilder.append(") ");
//			}
//
//			if (body != null && !"".equals(body)) { // 短消息内容
//				sqlBuilder.append("and ("); // 方便连接 "or"
//
//				StringTokenizer tokenizer = new StringTokenizer(body); // 空格分隔符
//				int i = 0;
//				while (tokenizer.hasMoreTokens()) {
//					String bodyEmp = tokenizer.nextToken();
//					StringTokenizer commaTokenizer = new StringTokenizer(
//							bodyEmp, ","); // 逗号分隔符
//					while (commaTokenizer.hasMoreTokens()) {
//						String bodyRst = commaTokenizer.nextToken(); // 最后分隔完成
//						if(i++ == 0){
//							sqlBuilder.append(" locate('" + bodyRst
//									+ "' , message_body) > 0 ");
//						} else {
//							sqlBuilder.append(" or locate('" + bodyRst
//									+ "' , message_body) > 0 ");
//						}
//					}
//				}
//				sqlBuilder.append(") ");
//			}
//
//			if (dateFrom != null) { // 日期范围
//				sqlBuilder.append("and message_date_time >= '" + dateFrom
//						+ "' ");
//			}
//			if (dateTo != null) { // 日期范围
//				sqlBuilder.append("and message_date_time <= '" + dateTo + "' ");
//			}
//
//			// 排序
//			if (orderBys != null && orderBys.length > 0) {
//				int length = orderBys.length;
//				String[] filteredKeywords = new String[oderByMap.keySet()
//						.size()]; // 过滤不是关键字的排序字符
//
//				List<String> keywordses = new ArrayList<String>();
//				for (int i = 0; i < length; i++) { // 获取有意义的关键字
//					String orderBy = orderBys[i];
//					if (!"".equals(orderBy)) {
//						String keywords = oderByMap.get(orderBy);
//						keywordses.add(keywords);
//					}
//				}
//
//				Iterator<String> iterator = keywordses.iterator();
//				sqlBuilder.append(" order by ");
//				int i = 0;
//				int size = keywordses.size();
//				while (iterator.hasNext()) {
//					String orderBy = (String) iterator.next();
//					if (!"".equals(orderBy)) {
//						sqlBuilder.append(orderBy);
//						if (i++ < size - 1) { // 如果不是最后一个关键字
//							sqlBuilder.append(", ");
//						}
//					}
//				}
//
//				// 倒序或升序
//				if (asc >= 1) {
//					sqlBuilder.append(" asc");
//				} else {
//					sqlBuilder.append(" desc");
//				}
//			}
//			sqlBuilder.append(" limit ?, ?");
////			System.out.println(sqlBuilder.toString());
//			log.debug("getMessageHistory sql: " + sqlBuilder.toString());
//			ps = connection.prepareStatement(sqlBuilder.toString());
//			ps.setInt(1, recordFromIndex);
//			ps.setInt(2, pageSize);
//			ResultSet messageResultSet = ps.executeQuery();
//			while (messageResultSet.next()) {
//				String messageId = new StringBuilder().append(messageResultSet.getInt(1)).toString();
//				String longMessageIdStr = messageResultSet.getString(2);
//				String largestMessageIdStr = messageResultSet.getString(3);
//				String mssageFrom = messageResultSet.getString(4);
//				String mssageTo = messageResultSet.getString(5);
//				String messageSubject = messageResultSet.getString(6);
//				String messageBody = messageResultSet.getString(7);
//				// Date messageDateTime = new Date();
//				Date messageDateTime = new Date(messageResultSet
//						.getTimestamp(8).getTime());
//				if (longMessageIdStr != null) { // 如果包含长文本外键
////					System.out.println("longMessageIdStr ===== "
////							+ longMessageIdStr);
//					PreparedStatement longMessagePreparedStatement = null;
//					try {
//						int longMessageId = Integer.parseInt(longMessageIdStr);
//						longMessagePreparedStatement = connection
//								.prepareStatement(SELECT_LONG_MESSAGE_HISORY_SQL);
//						longMessagePreparedStatement.setInt(1, longMessageId);
//						ResultSet longMessageResultSet = longMessagePreparedStatement
//								.executeQuery();
//						if (longMessageResultSet.next()) {
//							messageBody = longMessageResultSet.getString(1);
//						}
//					} catch (NumberFormatException e) {
//						log.error(e.getMessage(), e);
//					} finally {
//						longMessagePreparedStatement.close();
//					}
//				} else if (largestMessageIdStr != null) { // 如果包含超长文本外键
////					System.out.println("largestMessageIdStr ===== "
////							+ largestMessageIdStr);
//					PreparedStatement largestMessagePreparedStatement = null;
//					try {
//						int largestMessageId = Integer
//								.parseInt(largestMessageIdStr);
//						largestMessagePreparedStatement = connection
//								.prepareStatement(SELECT_LONG_MESSAGE_HISORY_SQL);
//						largestMessagePreparedStatement.setInt(1,
//								largestMessageId);
//						ResultSet largestMessageResultSet = largestMessagePreparedStatement
//								.executeQuery();
//						if (largestMessageResultSet.next()) {
//							messageBody = largestMessageResultSet.getString(1);
//						}
//					} catch (NumberFormatException e) {
//						log.error(e.getMessage(), e);
//					} finally {
//						largestMessagePreparedStatement.close();
//					}
//				}
//				MessageVO messageVO = new MessageVO();
//				messageVO.setMessageId(messageId);
//				messageVO.setMessageFrom(mssageFrom);
//				messageVO.setMessageTo(mssageTo);
//				messageVO.setMessageSubject(messageSubject);
//				messageVO.setMessageBody(messageBody);
//				messageVO.setMessageDateTime(messageDateTime);
//				messageVOs.add(messageVO);
//			}
//			pageVO.setPageCount(pageCount);
//			pageVO.setPageNumber(pageNumber);
//			pageVO.setRecordCount(recordCount);
//			pageVO.setPageSize(pageSize);
//			pageVO.setPageContents(messageVOs);
//
//			/**
//			 * 长消息检索 ps = connection.prepareStatement(
//			 * FUZZY_SELECT_LONG_MESSAGE_HISORY_SQL); ps.setString(1, body);
//			 * ResultSet longMessageResultSet = ps.executeQuery();
//			 * while(longMessageResultSet.next()){ int longMessageId =
//			 * longMessageResultSet.getInt(1); String longMessageBody =
//			 * longMessageResultSet.getString(2); ps =
//			 * connection.prepareStatement
//			 * (SELECT_MESSAGE_HISTORY_BY_LONG_MESSAGE_ID); ps.setInt(1,
//			 * longMessageId); ResultSet empLongMessageResultSet =
//			 * ps.executeQuery(); while(empLongMessageResultSet.next()){
//			 * 
//			 * } }
//			 */
//		} catch (SQLException e) {
//			log.error(e.getMessage(), e);
//			log.error("SQLState: " + e.getSQLState());
//		} finally {
//			DbConnectionManager.closeConnection(ps, connection);
//		}
		return pageVO;
	}

	@Override
	public PageVO getMessageHistoryByUserName(PageVO inputPageVO,
			MessageVO inputMessageVO, String dateFrom, String dateTo,
			String userName, String relationUserName, String[] orderBys, int asc) {
		if (oderByMap == null) { // 排序关键字集合
			oderByMap = new HashMap<String, String>();
			oderByMap.put("message_id", "message_id");
			oderByMap.put("message_from", "message_from");
			oderByMap.put("message_to", "message_to");
			oderByMap.put("message_subject", "message_subject");
			oderByMap.put("message_body", "message_body");
			oderByMap.put("message_date_time", "message_date_time");
			oderByMap.put("messageId", "message_id");
			oderByMap.put("messageFrom", "message_from");
			oderByMap.put("messageTo", "message_to");
			oderByMap.put("messageSubject", "message_subject");
			oderByMap.put("messageBody", "message_body");
			oderByMap.put("messageDateTime", "message_date_time");
			oderByMap.put("messageDateTimeStr", "message_date_time");
			oderByMap.put("userName", "message_from, message_to");
			oderByMap.put("relationUserName", "message_from, message_to");
		}

		ArrayList<MessageVO> messageVOs = new ArrayList<MessageVO>();
		PageVO pageVO = new PageVO();
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			connection = getConnection();
//			connection = DBConnection.getConnection();
			// ------------------------------- 查询条件 -------------------------------
			StringBuilder conditions = new StringBuilder(); // 查询条件sql语句
			
			String subject = null;
			String body = null;
			if (inputMessageVO != null) {
				subject = inputMessageVO.getMessageSubject();
				body = inputMessageVO.getMessageBody();

			}
			if(userName != null && !"".equals(userName)){
				if(relationUserName != null && !"".equals(relationUserName)){ // 如果有关联人，则拼合关联查询
					conditions.append(" and ( (locate('" + relationUserName
							+ "' , message_from) > 0 and locate('" + userName
							+ "' , message_to) > 0) or (locate('" + userName
							+ "' , message_from) > 0 and locate('" + relationUserName
							+ "' , message_to) > 0))");
				} else {
					conditions.append(" and (locate('" + userName
							+ "' , message_to) > 0 or locate('" + userName
							+ "' , message_from) > 0)");
				}
			}

			if (subject != null && !"".equals(subject)) { // 短消息标题
				conditions.append(" and ( "); // 方便连接 "or"

				StringTokenizer tokenizer = new StringTokenizer(subject); // 空格分隔符
				int i = 0;
				while (tokenizer.hasMoreTokens()) {
					String subjectEmp = tokenizer.nextToken();
					StringTokenizer commaTokenizer = new StringTokenizer(
							subjectEmp, ","); // 逗号分隔符
					while (commaTokenizer.hasMoreTokens()) {
						String subjectRst = commaTokenizer.nextToken(); // 最后分隔完成
						
						if(i++ == 0){
							conditions.append(" locate('" + subjectRst
									+ "' , message_subject) > 0 ");
						} else {
							conditions.append(" or locate('" + subjectRst
									+ "' , message_subject) > 0 ");
						}
						
					}
				}
				conditions.append(") ");
			}

			if (body != null && !"".equals(body)) { // 短消息内容
				conditions.append("and ("); // 方便连接 "or"

				StringTokenizer tokenizer = new StringTokenizer(body); // 空格分隔符
				int i = 0;
				while (tokenizer.hasMoreTokens()) {
					String bodyEmp = tokenizer.nextToken();
					StringTokenizer commaTokenizer = new StringTokenizer(
							bodyEmp, ","); // 逗号分隔符
					while (commaTokenizer.hasMoreTokens()) {
						String bodyRst = commaTokenizer.nextToken(); // 最后分隔完成
						if(i++ == 0){
							conditions.append(" locate('" + bodyRst
									+ "' , message_body) > 0 ");
						} else {
							conditions.append(" or locate('" + bodyRst
									+ "' , message_body) > 0 ");
						}
					}
				}
				conditions.append(") ");
			}

			if (dateFrom != null) { // 日期范围
				conditions.append("and message_date_time >= '" + dateFrom
						+ "' ");
			}
			if (dateTo != null) { // 日期范围
				conditions.append("and message_date_time <= '" + dateTo + "' ");
			}
			//end ------------------------------- 查询条件结束 -------------------------------
			
			
			
			//start ------------------------------- 查询总数 -------------------------------
			int recordCount = 0;
			StringBuilder recordCountSQL = new StringBuilder(RECORD_COUNT_SQL);
			recordCountSQL.append(conditions);
			ps = connection.prepareStatement(recordCountSQL.toString());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				recordCount = rs.getInt(1);
			}

			int pageNumber = inputPageVO.getPageNumber(); // 页码
			int pageSize = inputPageVO.getPageSize(); // 分页记录数
			if (pageSize <= 0) {
				pageSize = 20;
			}
			int pageCount = recordCount / pageSize; // 总分页数
			int leastRecord = recordCount % pageSize; // 如果不能整除
			if (leastRecord > 0) {
				pageCount++;
			}
			if (pageNumber > pageCount) {
				pageNumber = pageCount;
			}
			int recordFromIndex = (pageNumber - 1) * pageSize; // 起始索引
			//end ------------------------------- 查询总数结束 -------------------------------
			
			
			
			// ------------------------------- 短消息检索 -------------------------------
			if(recordCount > 0){
				StringBuilder sqlBuilder = new StringBuilder(
						SELECT_MESSAGE_HISTORY_SQL);
				sqlBuilder.append(conditions);
				// 排序
				if (orderBys != null && orderBys.length > 0) {
					int length = orderBys.length;
					
					List<String> keywordses = new ArrayList<String>();
					for (int i = 0; i < length; i++) { // 获取有意义的关键字
						String orderBy = orderBys[i];
						if (!"".equals(orderBy)) {
							String keywords = oderByMap.get(orderBy);
							keywordses.add(keywords);
						}
					}
					
					Iterator<String> iterator = keywordses.iterator();
					sqlBuilder.append(" order by ");
					int i = 0;
					int size = keywordses.size();
					while (iterator.hasNext()) {
						String orderBy = (String) iterator.next();
						if (!"".equals(orderBy)) {
							sqlBuilder.append(orderBy);
							if (i++ < size - 1) { // 如果不是最后一个关键字
								sqlBuilder.append(", ");
							}
						}
					}
					
					// 倒序或升序
					if (asc >= 1) {
						sqlBuilder.append(" asc");
					} else {
						sqlBuilder.append(" desc");
					}
				}
				sqlBuilder.append(" limit ?, ?");
//			System.out.println(sqlBuilder.toString());
				log.debug("getMessageHistory sql: " + sqlBuilder.toString());
//				System.out.println("getMessageHistory sql: " + sqlBuilder.toString());
				ps = connection.prepareStatement(sqlBuilder.toString());
				ps.setInt(1, recordFromIndex);
				ps.setInt(2, pageSize);
				ResultSet messageResultSet = ps.executeQuery();
				while (messageResultSet.next()) {
					long messageId = messageResultSet.getLong(1);
					String longMessageIdStr = messageResultSet.getString(2);
					String largestMessageIdStr = messageResultSet.getString(3);
					String mssageFrom = messageResultSet.getString(4);
					String mssageTo = messageResultSet.getString(5);
					String messageSubject = messageResultSet.getString(6);
					String messageBody = messageResultSet.getString(7);
					// Date messageDateTime = new Date();
					Date messageDateTime = new Date(messageResultSet
							.getTimestamp(8).getTime());
					String sendMessageId = messageResultSet.getString(9);
					String messageType = messageResultSet.getString(10);
					
					if (longMessageIdStr != null) { // 如果包含长文本外键
//					System.out.println("longMessageIdStr ===== "
//							+ longMessageIdStr);
						PreparedStatement longMessagePreparedStatement = null;
						try {
							int longMessageId = Integer.parseInt(longMessageIdStr);
							longMessagePreparedStatement = connection
									.prepareStatement(SELECT_LONG_MESSAGE_HISORY_SQL);
							longMessagePreparedStatement.setInt(1, longMessageId);
							ResultSet longMessageResultSet = longMessagePreparedStatement
									.executeQuery();
							if (longMessageResultSet.next()) {
								messageBody = longMessageResultSet.getString(1);
							}
						} catch (NumberFormatException e) {
							log.error(e.getMessage(), e);
						} finally {
							longMessagePreparedStatement.close();
						}
					} else if (largestMessageIdStr != null) { // 如果包含超长文本外键
//					System.out.println("largestMessageIdStr ===== "
//							+ largestMessageIdStr);
						PreparedStatement largestMessagePreparedStatement = null;
						try {
							int largestMessageId = Integer
									.parseInt(largestMessageIdStr);
							largestMessagePreparedStatement = connection
									.prepareStatement(SELECT_LARGEST_MESSAGE_HISORY_SQL);
							largestMessagePreparedStatement.setInt(1,
									largestMessageId);
							ResultSet largestMessageResultSet = largestMessagePreparedStatement
									.executeQuery();
							if (largestMessageResultSet.next()) {
								messageBody = largestMessageResultSet.getString(1);
							}
						} catch (NumberFormatException e) {
							log.error(e.getMessage(), e);
						} finally {
							largestMessagePreparedStatement.close();
						}
					}
					MessageVO messageVO = new MessageVO();
					messageVO.setMessageId(messageId);
					messageVO.setMessageFrom(mssageFrom);
					messageVO.setMessageTo(mssageTo);
					messageVO.setMessageSubject(messageSubject);
					messageVO.setMessageBody(messageBody);
					messageVO.setMessageDateTime(messageDateTime);
					messageVO.setMessageType(messageType);
					messageVO.setSendMessageId(sendMessageId);
					messageVOs.add(messageVO);
				}
			}
			pageVO.setPageCount(pageCount);
			pageVO.setPageNumber(pageNumber);
			pageVO.setRecordCount(recordCount);
			pageVO.setPageSize(pageSize);
			pageVO.setPageContents(messageVOs);

			/**
			 * 长消息检索 ps = connection.prepareStatement(
			 * FUZZY_SELECT_LONG_MESSAGE_HISORY_SQL); ps.setString(1, body);
			 * ResultSet longMessageResultSet = ps.executeQuery();
			 * while(longMessageResultSet.next()){ int longMessageId =
			 * longMessageResultSet.getInt(1); String longMessageBody =
			 * longMessageResultSet.getString(2); ps =
			 * connection.prepareStatement
			 * (SELECT_MESSAGE_HISTORY_BY_LONG_MESSAGE_ID); ps.setInt(1,
			 * longMessageId); ResultSet empLongMessageResultSet =
			 * ps.executeQuery(); while(empLongMessageResultSet.next()){
			 * 
			 * } }
			 */
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		return pageVO;
	}
	@Override
	public MessageVO getMessageHistoryByMessageId(long messageId) {
		Connection connection = null;
		PreparedStatement ps = null;
		MessageVO messageVO = null;
		try {
			connection = getConnection();
			ps = connection.prepareStatement(SELECT_MESSAGE_HISTORY_BY_ID);
			ps.setLong(1, messageId);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				String longMessageIdStr = rs.getString(2); // SELECT  message_id, long_message_id, largest_message_id, message_from, message_to, message_subject, message_body, message_date_time FROM kr_message_history where message_id = ?
				String largestMessageIdStr = rs.getString(3);
				String messageFrom = rs.getString(4);
				String messageTo = rs.getString(5);
				String messageSubject = rs.getString(6);
				String messageBody = rs.getString(7);
				Date messageDateTime = new Date(rs.getTimestamp(8).getTime());
				if (longMessageIdStr != null) { // 如果包含长文本外键
//				System.out.println("longMessageIdStr ===== "
//						+ longMessageIdStr);
					PreparedStatement longMessagePreparedStatement = null;
					try {
						int longMessageId = Integer.parseInt(longMessageIdStr);
						longMessagePreparedStatement = connection
								.prepareStatement(SELECT_LONG_MESSAGE_HISORY_SQL);
						longMessagePreparedStatement.setInt(1, longMessageId);
						ResultSet longMessageResultSet = longMessagePreparedStatement
								.executeQuery();
						if (longMessageResultSet.next()) {
							messageBody = longMessageResultSet.getString(1);
						}
					} catch (NumberFormatException e) {
						log.error(e.getMessage(), e);
					} finally {
						longMessagePreparedStatement.close();
					}
				} else if (largestMessageIdStr != null) { // 如果包含超长文本外键
//				System.out.println("largestMessageIdStr ===== "
//						+ largestMessageIdStr);
					PreparedStatement largestMessagePreparedStatement = null;
					try {
						int largestMessageId = Integer
								.parseInt(largestMessageIdStr);
						largestMessagePreparedStatement = connection
								.prepareStatement(SELECT_LARGEST_MESSAGE_HISORY_SQL);
						largestMessagePreparedStatement.setInt(1,
								largestMessageId);
						ResultSet largestMessageResultSet = largestMessagePreparedStatement
								.executeQuery();
						if (largestMessageResultSet.next()) {
							messageBody = largestMessageResultSet.getString(1);
						}
					} catch (NumberFormatException e) {
						log.error(e.getMessage(), e);
					} finally {
						largestMessagePreparedStatement.close();
					}
				}
				messageVO = new MessageVO();
				messageVO.setMessageId(messageId);
				messageVO.setMessageFrom(messageFrom);
				messageVO.setMessageTo(messageTo);
				messageVO.setMessageSubject(messageSubject);
				messageVO.setMessageBody(messageBody);
				messageVO.setMessageDateTime(messageDateTime);
			}
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		return messageVO;
	}
	
	public static void main(String[] args) {
		PageVO pageVO = new PageVO();
		pageVO.setPageNumber(1);
		pageVO.setPageSize(100);
		MessageVO messageVO = new MessageVO();
		messageVO.setMessageFrom("");
		messageVO.setMessageTo("熊瑛琪");
		messageVO.setMessageDateTime(new Date());
		PageVO pageVO2 = new MessageDAOImpl().getMessageHistory(pageVO,
				messageVO, "2012-02-03 00:00:00", "2013-07-30 23:59:59",
				new String[] { "messageDateTimeStr" }, -1);

		JsonConfig config = new JsonConfig();
		config.setExcludes(new String[] { "messageDateTime" });
		JSONArray jsonArray = JSONArray.fromObject(pageVO2, config);
		System.out.println(jsonArray);
		
//		new MessageDAOImpl().getMessageHistoryByUserName(pageVO, null, dateFrom, dateTo, userName, relationUserName, orderBys, asc)
		
		
		
//		MessageVO messageVO = new MessageVO(0, "熊瑛琪", "zhangjia", "class com.kingray.openfire.plugin.vo.PageVO", Message.Type.chat + "");
//		new MessageDAOImpl().addMessageHistory(messageVO);
//		
//		PageVO pageVO3 = new PageVO();
//		pageVO3.setPageNumber(1);
//		pageVO3.setPageSize(100);
//		MessageVO messageVO2 = new MessageVO();
//		messageVO2.setMessageFrom("");
//		messageVO2.setMessageTo("熊瑛琪");
//		messageVO2.setMessageDateTime(new Date());
//		PageVO pageVOResult = new MessageDAOImpl().getMessageHistoryByUserName(pageVO3, messageVO2, null, null, "熊瑛琪", null, null, -1);
//		System.out.println(pageVOResult);
		// StringTokenizer tokenizer = new StringTokenizer("aaa,bbbbbb,ccccc");
		// while(tokenizer.hasMoreTokens()){
		// String bodyEmp = tokenizer.nextToken();
		// StringTokenizer commaTokenizer = new StringTokenizer(bodyEmp, ",");
		// while(commaTokenizer.hasMoreTokens()){
		// String bodyRst = commaTokenizer.nextToken();
		// System.out.println(bodyRst);
		// }
		// }
	}

	/**
	 * <br>2013-6-17 上午11:47:00
	 * @see com.kingray.openfire.plugin.dao.MessageDAO#getMessageHistoryCountByUserName(java.lang.String, java.lang.String, java.util.Date)
	 */
	@Override
	public int getMessageHistoryCountByUserName(String userName, String relationUserName, Date queryDate) {
		Connection connection = null;
		PreparedStatement ps = null;
		int messageCount = -1;
		try {
			connection = getConnection();
			if(queryDate != null){
				Date queryEndDate = new Date(queryDate.getTime() + 24 * CalendarHelper.HOUR - 1); // 查询的结束日期
				ps = connection.prepareStatement(SELECT_MESSAGE_HISTORY_COUNT_BY_USER_NAME + " and message_date_time between ? and ? ");
//				ps.setTime(5, new Time(queryDate.getTime()));
//				ps.setTime(6, new Time(queryEndDate.getTime()));
				ps.setString(5, DateHelper.dateToStrLong(queryDate));
				ps.setString(6, DateHelper.dateToStrLong(queryEndDate));
			} else {
				ps = connection.prepareStatement(SELECT_MESSAGE_HISTORY_COUNT_BY_USER_NAME);
			}
			if(relationUserName == null){
				relationUserName = "";
			}
			relationUserName = "%" + relationUserName + "%";
			ps.setString(1, userName);
			ps.setString(2, relationUserName);
			ps.setString(3, relationUserName);
			ps.setString(4, userName);
			
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				messageCount = rs.getInt(1);
			}
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		return messageCount;
	}

	/**
	 * <br>2013-6-17 下午3:58:34
	 * @see com.kingray.openfire.plugin.dao.MessageDAO#getMessageHistorySummaryByUserName(java.lang.String, java.lang.String, java.util.Date)
	 */
	@Override
	public Collection<MessageVO> getMessageHistorySummaryByUserName(
			String userName, String relationUserName, Date queryDate) {
		Connection connection = null;
		PreparedStatement ps = null;
		Collection<MessageVO> messageVOs = null;
		try {
			connection = getConnection();
			if(queryDate != null){
				Date queryEndDate = new Date(queryDate.getTime() + 24 * CalendarHelper.HOUR - 1); // 查询的结束日期
				ps = connection.prepareStatement(SELECT_MESSAGE_HISTORY_SUMMARY_BY_USER_NAME + " and message_date_time between ? and ? ");
//				ps.setTime(5, new Time(queryDate.getTime()));
//				ps.setTime(6, new Time(queryEndDate.getTime()));
				ps.setString(5, DateHelper.dateToStrLong(queryDate));
				ps.setString(6, DateHelper.dateToStrLong(queryEndDate));
			} else {
				ps = connection.prepareStatement(SELECT_MESSAGE_HISTORY_SUMMARY_BY_USER_NAME);
			}
			if(relationUserName == null){
				relationUserName = "";
			}
			relationUserName = "%" + relationUserName + "%";
			ps.setString(1, userName);
			ps.setString(2, relationUserName);
			ps.setString(3, relationUserName);
			ps.setString(4, userName);
			
			ResultSet rs = ps.executeQuery();
			messageVOs = new HashSet<MessageVO>();
			while (rs.next()) {
				long messageId = rs.getLong(1);
				String sendMessageId = rs.getString(2);
				MessageVO messageVO = new MessageVO();
				messageVO.setMessageId(messageId);
				messageVO.setSendMessageId(sendMessageId);
				messageVOs.add(messageVO);
			}
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		return messageVOs;
	}

	/**
	 * <br>2013-6-18 上午10:00:38
	 * @see com.kingray.openfire.plugin.dao.MessageDAO#getMessageHistoryDetailByUserName(java.lang.String, java.lang.String, java.util.Date)
	 */
	@Override
	public Collection<MessageVO> getMessageHistoryDetailByUserName(
			String userName, String relationUserName, Date queryDate) {
		Connection connection = null;
		PreparedStatement ps = null;
		Collection<MessageVO> messageVOs = null;
		try {
			connection = getConnection();
			if(queryDate != null){
				Date queryEndDate = new Date(queryDate.getTime() + 24 * CalendarHelper.HOUR - 1); // 查询的结束日期
				ps = connection.prepareStatement(SELECT_MESSAGE_HISTORY_DETAIL_BY_USER_NAME + " and message_date_time between ? and ? ");
//				ps.setTime(5, new Time(queryDate.getTime()));
//				ps.setTime(6, new Time(queryEndDate.getTime()));
				ps.setString(5, DateHelper.dateToStrLong(queryDate));
				ps.setString(6, DateHelper.dateToStrLong(queryEndDate));
			} else {
				ps = connection.prepareStatement(SELECT_MESSAGE_HISTORY_DETAIL_BY_USER_NAME);
			}
			if(relationUserName == null){
				relationUserName = "";
			}
			relationUserName = "%" + relationUserName + "%";
			ps.setString(1, userName);
			ps.setString(2, relationUserName);
			ps.setString(3, relationUserName);
			ps.setString(4, userName);
			
			ResultSet rs = ps.executeQuery();
			messageVOs = new HashSet<MessageVO>();
			while (rs.next()) { //select message_id, long_message_id, largest_message_id, message_from, message_to, message_subject, message_body, message_date_time, send_message_id, message_type from kr_message_history where ((message_from = ? and message_to like ?) or (message_from like ? and message_to = ?)) 
				long messageId = rs.getLong(1);
				String longMessageIdStr = rs.getString(2);
				String largestMessageIdStr = rs.getString(3);
				String messageFrom = rs.getString(4);
				String messageTo = rs.getString(5);
				String messageSubject = rs.getString(6);
				String messageBody =  rs.getString(7);
				Date messageDateTime = new Date(rs.getTimestamp(8).getTime());
				String sendMessageId = rs.getString(9);
				String messageType = rs.getString(10);
				
				if(StringHelper.notNullAndNotEmpty(longMessageIdStr)){ // 如果长消息字段不为空，则获取长消息内容
					PreparedStatement psLongMessage = null;
					try {
						psLongMessage = connection.prepareStatement(SELECT_LONG_MESSAGE_HISORY_SQL);// select long_message_body from kr_long_message_history where long_message_id = ?
						psLongMessage.setLong(1, Long.parseLong(longMessageIdStr));
						ResultSet rsLongMessage = psLongMessage.executeQuery();
						if (rsLongMessage.next()) {
							messageBody = rsLongMessage.getString(1);
						}
					} catch (Exception e) {
						e.printStackTrace();
					} finally {
						DbConnectionManager.closeStatement(psLongMessage);
					}
				} else if(StringHelper.notNullAndNotEmpty(largestMessageIdStr)){ // 如果超长消息字段不为空，则获取超长消息内容
					PreparedStatement psLargestMessage = null;
					try {
						psLargestMessage = connection.prepareStatement(SELECT_LARGEST_MESSAGE_HISORY_SQL);// select largest_message_body from kr_largest_message_history where largest_message_id = ?
						psLargestMessage.setLong(1, Long.parseLong(largestMessageIdStr));
						ResultSet rsLargestMessage = psLargestMessage.executeQuery();
						if (rsLargestMessage.next()) {
							messageBody = rsLargestMessage.getString(1);
						}
					} catch (Exception e) {
						e.printStackTrace();
					} finally {
						DbConnectionManager.closeStatement(psLargestMessage);
					}
				}
				MessageVO messageVO = new MessageVO();
				messageVO.setMessageId(messageId);
				messageVO.setMessageFrom(messageFrom);
				messageVO.setMessageTo(messageTo);
				messageVO.setMessageSubject(messageSubject);
				messageVO.setMessageBody(messageBody);
				messageVO.setMessageDateTime(messageDateTime);
				messageVO.setSendMessageId(sendMessageId);
				messageVO.setMessageType(messageType);
				messageVOs.add(messageVO);
			}
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		return messageVOs;
	}

	/**
	 * <br>2013-6-18 上午10:51:18
	 * @see com.kingray.openfire.plugin.dao.MessageDAO#getMessageHistoryDetailByMessageIds(java.util.Collection)
	 */
	@Override
	public Collection<MessageVO> getMessageHistoryDetailByMessageIds(
			Collection<Long> messageIds) {
		Connection connection = null;
		PreparedStatement ps = null;
		Collection<MessageVO> messageVOs = null;
		try {
			connection = getConnection();
			ps = connection.prepareStatement(SELECT_MESSAGE_HISTORY_DETAIL_BY_MESSAGE_ID);
			messageVOs = new HashSet<MessageVO>();
			
			for (Iterator<Long> iterator = messageIds.iterator(); iterator.hasNext();) {
				Long messageIdQuery = iterator.next();
				ps.setLong(1, messageIdQuery);
				ResultSet rs = ps.executeQuery();
				while (rs.next()) { //select message_id, long_message_id, largest_message_id, message_from, message_to, message_subject, message_body, message_date_time, send_message_id, message_type from kr_message_history where ((message_from = ? and message_to like ?) or (message_from like ? and message_to = ?)) 
					long messageId = rs.getLong(1);
					String longMessageIdStr = rs.getString(2);
					String largestMessageIdStr = rs.getString(3);
					String messageFrom = rs.getString(4);
					String messageTo = rs.getString(5);
					String messageSubject = rs.getString(6);
					String messageBody =  rs.getString(7);
					Date messageDateTime = new Date(rs.getTimestamp(8).getTime());
					String sendMessageId = rs.getString(9);
					String messageType = rs.getString(10);
					
					if(StringHelper.notNullAndNotEmpty(longMessageIdStr)){ // 如果长消息字段不为空，则获取长消息内容
						PreparedStatement psLongMessage = null;
						try {
							psLongMessage = connection.prepareStatement(SELECT_LONG_MESSAGE_HISORY_SQL);// select long_message_body from kr_long_message_history where long_message_id = ?
							psLongMessage.setLong(1, Long.parseLong(longMessageIdStr));
							ResultSet rsLongMessage = psLongMessage.executeQuery();
							if (rsLongMessage.next()) {
								messageBody = rsLongMessage.getString(1);
							}
						} catch (Exception e) {
							e.printStackTrace();
						} finally {
							DbConnectionManager.closeStatement(psLongMessage);
						}
					} else if(StringHelper.notNullAndNotEmpty(largestMessageIdStr)){ // 如果超长消息字段不为空，则获取超长消息内容
						PreparedStatement psLargestMessage = null;
						try {
							psLargestMessage = connection.prepareStatement(SELECT_LARGEST_MESSAGE_HISORY_SQL);// select largest_message_body from kr_largest_message_history where largest_message_id = ?
							psLargestMessage.setLong(1, Long.parseLong(largestMessageIdStr));
							ResultSet rsLargestMessage = psLargestMessage.executeQuery();
							if (rsLargestMessage.next()) {
								messageBody = rsLargestMessage.getString(1);
							}
						} catch (Exception e) {
							e.printStackTrace();
						} finally {
							DbConnectionManager.closeStatement(psLargestMessage);
						}
					}
					MessageVO messageVO = new MessageVO();
					messageVO.setMessageId(messageId);
					messageVO.setMessageFrom(messageFrom);
					messageVO.setMessageTo(messageTo);
					messageVO.setMessageSubject(messageSubject);
					messageVO.setMessageBody(messageBody);
					messageVO.setMessageDateTime(messageDateTime);
					messageVO.setSendMessageId(sendMessageId);
					messageVO.setMessageType(messageType);
					messageVOs.add(messageVO);
				}
			}
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		return messageVOs;
	}

	/**
	 * <br>2013-6-18 上午10:51:18
	 * @see com.kingray.openfire.plugin.dao.MessageDAO#getMessageHistoryDetailBySendMessageIds(java.util.Collection)
	 */
	@Override
	public Collection<MessageVO> getMessageHistoryDetailBySendMessageIds(
			Collection<String> sendMessageIds) {
		Connection connection = null;
		PreparedStatement ps = null;
		Collection<MessageVO> messageVOs = null;
		try {
			connection = getConnection();
			ps = connection.prepareStatement(SELECT_MESSAGE_HISTORY_DETAIL_BY_SEND_MESSAGE_ID);
			messageVOs = new HashSet<MessageVO>();
			
			for (Iterator<String> iterator = sendMessageIds.iterator(); iterator.hasNext();) {
				String sendMessageIdQuery = iterator.next();
				ps.setString(1, sendMessageIdQuery);
				ResultSet rs = ps.executeQuery();
				while (rs.next()) {
					long messageId = rs.getLong(1);
					String longMessageIdStr = rs.getString(2);
					String largestMessageIdStr = rs.getString(3);
					String messageFrom = rs.getString(4);
					String messageTo = rs.getString(5);
					String messageSubject = rs.getString(6);
					String messageBody =  rs.getString(7);
					Date messageDateTime = new Date(rs.getTimestamp(8).getTime());
					String sendMessageId = rs.getString(9);
					String messageType = rs.getString(10);
					
					if(StringHelper.notNullAndNotEmpty(longMessageIdStr)){ // 如果长消息字段不为空，则获取长消息内容
						PreparedStatement psLongMessage = null;
						try {
							psLongMessage = connection.prepareStatement(SELECT_LONG_MESSAGE_HISORY_SQL);// select long_message_body from kr_long_message_history where long_message_id = ?
							psLongMessage.setLong(1, Long.parseLong(longMessageIdStr));
							ResultSet rsLongMessage = psLongMessage.executeQuery();
							if (rsLongMessage.next()) {
								messageBody = rsLongMessage.getString(1);
							}
						} catch (Exception e) {
							e.printStackTrace();
						} finally {
							DbConnectionManager.closeStatement(psLongMessage);
						}
					} else if(StringHelper.notNullAndNotEmpty(largestMessageIdStr)){ // 如果超长消息字段不为空，则获取超长消息内容
						PreparedStatement psLargestMessage = null;
						try {
							psLargestMessage = connection.prepareStatement(SELECT_LARGEST_MESSAGE_HISORY_SQL);// select largest_message_body from kr_largest_message_history where largest_message_id = ?
							psLargestMessage.setLong(1, Long.parseLong(largestMessageIdStr));
							ResultSet rsLargestMessage = psLargestMessage.executeQuery();
							if (rsLargestMessage.next()) {
								messageBody = rsLargestMessage.getString(1);
							}
						} catch (Exception e) {
							e.printStackTrace();
						} finally {
							DbConnectionManager.closeStatement(psLargestMessage);
						}
					}
					MessageVO messageVO = new MessageVO();
					messageVO.setMessageId(messageId);
					messageVO.setMessageFrom(messageFrom);
					messageVO.setMessageTo(messageTo);
					messageVO.setMessageSubject(messageSubject);
					messageVO.setMessageBody(messageBody);
					messageVO.setMessageDateTime(messageDateTime);
					messageVO.setSendMessageId(sendMessageId);
					messageVO.setMessageType(messageType);
					messageVOs.add(messageVO);
				}
			}
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			log.error("SQLState: " + e.getSQLState());
		} finally {
			DbConnectionManager.closeConnection(ps, connection);
		}
		return messageVOs;
	}

}
